import sqlite3
import os
import time

print("===== 数据库诊断工具 =====")
print(f"当前工作目录: {os.getcwd()}")
db_path = 'health_bed_data.db'
print(f"数据库文件路径: {os.path.abspath(db_path)}")
print(f"数据库文件是否存在: {'是' if os.path.exists(db_path) else '否'}")

if os.path.exists(db_path):
    print(f"数据库大小: {os.path.getsize(db_path) / 1024:.2f} KB")
    print(f"最后修改时间: {time.ctime(os.path.getmtime(db_path))}")

# 连接到数据库
conn = sqlite3.connect('health_bed_data.db')
cursor = conn.cursor()

# 检查表结构
print("\n===== 数据库表结构 =====")
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()
print(f"数据库中的表: {[table[0] for table in tables]}")

# 检查每个表的记录数
for table in tables:
    try:
        table_name = table[0]
        cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
        count = cursor.fetchone()[0]
        print(f"表 '{table_name}' 的记录数: {count}")
    except Exception as e:
        print(f"无法获取表 '{table_name}' 的记录数: {e}")

# 检查物模型数据表
try:
    print("\n===== 物模型数据表 =====")
    cursor.execute("PRAGMA table_info(property_model_data)")
    columns = cursor.fetchall()
    print(f"字段数量: {len(columns)}")
    for col in columns:
        print(f"- {col[1]} ({col[2]})")
    
    # 查询数据数量
    cursor.execute("SELECT COUNT(*) FROM property_model_data")
    count = cursor.fetchone()[0]
    print(f"\n物模型数据记录数: {count}")
    
    # 如果有数据，显示一些示例
    if count > 0:
        cursor.execute("SELECT timestamp, identifier, value, numeric_value, is_valid, data_quality FROM property_model_data LIMIT 5")
        rows = cursor.fetchall()
        print("\n示例数据:")
        for row in rows:
            print(f"时间: {row[0]}, 字段: {row[1]}, 值: {row[2]}, 数值: {row[3]}, 有效: {row[4]}, 质量: {row[5]}")
    else:
        print("\n尝试手动插入测试数据...")
        try:
            cursor.execute(
                """INSERT INTO property_model_data 
                   (timestamp, identifier, value, numeric_value, data_type, name, time, is_valid, data_quality) 
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""", 
                (time.strftime('%Y-%m-%dT%H:%M:%S'), 
                 'test_field', 
                 'test_value',
                 123.45,
                 'string',
                 'Test Field',
                 str(int(time.time() * 1000)),
                 1,
                 100)
            )
            conn.commit()
            print("测试数据插入成功！")
            
            # 再次检查是否有数据
            cursor.execute("SELECT COUNT(*) FROM property_model_data")
            count = cursor.fetchone()[0]
            print(f"插入测试数据后的记录数: {count}")
            
            if count > 0:
                cursor.execute("SELECT timestamp, identifier, value, numeric_value, is_valid, data_quality FROM property_model_data")
                rows = cursor.fetchall()
                print("\n所有数据:")
                for row in rows:
                    print(f"时间: {row[0]}, 字段: {row[1]}, 值: {row[2]}, 数值: {row[3]}, 有效: {row[4]}, 质量: {row[5]}")
        except Exception as e:
            print(f"手动插入测试数据失败: {e}")
except Exception as e:
    print(f"查询物模型数据表出错: {e}")

# 检查设备数据表
try:
    print("\n===== 设备数据表 =====")
    cursor.execute("PRAGMA table_info(device_data)")
    columns = cursor.fetchall()
    print(f"字段数量: {len(columns)}")
    for col in columns:
        print(f"- {col[1]} ({col[2]})")
    
    # 查询数据数量
    cursor.execute("SELECT COUNT(*) FROM device_data")
    count = cursor.fetchone()[0]
    print(f"\n设备数据记录数: {count}")
except Exception as e:
    print(f"查询设备数据表出错: {e}")

# 关闭连接
conn.close()
